package com.rtu.web;

import java.sql.ResultSet;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.rtu.dao.IDyRainGatherDataDao;
import com.rtu.dao.IRainGatherDataDao;
import com.rtu.domain.RainGatherData;
import com.rtu.domain.Station;
import com.rtu.dto.QueryStationDto;
import com.rtu.dto.QueryStationRainReportDto;
import com.rtu.dto.response.ApiResponse;
import com.rtu.enums.Code;
import com.rtu.service.IDyRainStationService;
import com.rtu.service.IStationService;
import com.rtu.utils.DateUtil;
import com.rtu.utils.JDBCUtil;
import com.rtu.utils.ResultSetMapper;

@Controller
@RequestMapping("/dyrain/station")
public class DyRainStationController {

	private static final Logger logger = LoggerFactory.getLogger(DyRainStationController.class);

	@Resource
	private IDyRainStationService stationService;
	@Resource
	private IDyRainGatherDataDao rainGatherDataDao;
	
    @RequestMapping("/getPageList")
    @ResponseBody
    public ApiResponse getPageList(QueryStationDto dto){
        ApiResponse apiResponse;
        try {
            if (dto.getPage() == null) {
                dto.setPage(1);
            }
            if (dto.getPageRowCount() == null) {
                dto.setPageRowCount(50);
            }
            Map map = stationService.query(dto);
            apiResponse = ApiResponse.create(true, Code.SUCCESS,map);
        }catch (Exception e){
            logger.error("getList",e);
            apiResponse = ApiResponse.create(false,e.getMessage(), Code.COMMON_SERVER_ERROR.code,null);
        }
        return apiResponse;
    }
    
    @RequestMapping("/getStation")
    @ResponseBody
    public ApiResponse getStation(QueryStationDto dto){
        ApiResponse apiResponse;
        try {
            if (dto == null || dto.getStationId() == null || dto.getStationId() == 0) {
                apiResponse = ApiResponse.create(false,null, Code.BAD_PARAMS.code,null);
                return apiResponse;
            }
            Map map = stationService.queryStationById(dto.getStationId());
            if (map == null || map.get("station") == null) {
                apiResponse = ApiResponse.create(false,null, Code.NO_PROJECT.code,null);
                return apiResponse;
            }
            apiResponse = ApiResponse.create(true, Code.SUCCESS,map);
        }catch (Exception e){
            logger.error("getStation",e);
            apiResponse = ApiResponse.create(false,e.getMessage(),Code.COMMON_SERVER_ERROR.code,null);
        }
        return apiResponse;
    }
    
    @RequestMapping("/updateStation")
    @ResponseBody
    public ApiResponse updateStation(Station station){
    	ApiResponse apiResponse;
        try {
        	stationService.updateStation(station);
            apiResponse = ApiResponse.create(true, Code.SUCCESS,null);
        }catch (Exception e){
            logger.error("updateStation",e);
            apiResponse = ApiResponse.create(false,e.getMessage(), Code.COMMON_SERVER_ERROR.code,null);
        }
    	return apiResponse;
    }
    
    @RequestMapping("/createStation")
    @ResponseBody
    public ApiResponse createStation(Station station){
    	ApiResponse apiResponse;
    	JDBCUtil jdbcUtil = null;
        try {
        	stationService.insertStation(station);
        	int stationId = station.getId();
        	jdbcUtil = new JDBCUtil(station.getJdbcDriver(), station.getJdbcUrl(),station.getJdbcUserName(), station.getJdbcPassword());
        	String listSql = String.format(station.getGatherDataSql(), ">0");
            ResultSet resultSet = jdbcUtil.excuteQueryResultSet(listSql + " LIMIT 0,100", null);
            ResultSetMapper<RainGatherData> resultSetMapper = new ResultSetMapper<RainGatherData>(); 
            List<RainGatherData> rainList = resultSetMapper.mapRersultSetToObject(resultSet, RainGatherData.class);
            if(null != rainList && rainList.size() > 0) {
            	for(RainGatherData d : rainList) {
            		d.setStationId(stationId);
            	}
            	rainGatherDataDao.batchInsert(rainList);
            }
            
            apiResponse = ApiResponse.create(true, Code.SUCCESS,null);
        }catch (Exception e){
            logger.error("goUpdateProject",e);
            apiResponse = ApiResponse.create(false,e.getMessage(), Code.COMMON_SERVER_ERROR.code,null);
        } finally {
        	if(null != jdbcUtil) {
        		jdbcUtil.closeAll();
        	}
        }
    	return apiResponse;
    }
    
	@RequestMapping("/getList")
    @ResponseBody
    public ApiResponse getList(QueryStationRainReportDto dto){
        ApiResponse apiResponse;
        JDBCUtil jdbcUtil = null;
        try {
            if (dto == null || null == dto.getStationId()) {
                apiResponse = ApiResponse.create(false,null, Code.BAD_PARAMS.code,null);
                return apiResponse;
            }
            Map map = stationService.queryStationById(dto.getStationId());
            if (map == null || map.get("station") == null) {
                apiResponse = ApiResponse.create(false,null, Code.NO_PROJECT.code,null);
                return apiResponse;
            }
            Station station = (Station)map.get("station");
            String sql = "select va, date_format(create_time, '%Y-%m-%d %H:%i:%S') createTime from tb_dy_rainfall_data_gather";
            String condition = " where station_id = " + dto.getStationId();
            Map result = new HashMap();
    		if(StringUtils.isNotBlank(dto.getBeginTime())) {
    			condition = condition + " and create_time >='" + dto.getBeginTime() + "'";
    		}
    		if(StringUtils.isNotBlank(dto.getEndTime())) {
    			condition = condition + " and create_time <='" + dto.getEndTime() + "'";
    		}
            sql = sql +  " " + condition + "  order by create_time desc limit " + dto.getOffset() * dto.getRows() + " , " + dto.getRows();
            jdbcUtil = new JDBCUtil(station.getJdbcDriver(), station.getJdbcUrl(),station.getJdbcUserName(), station.getJdbcPassword());
            List<Object> listResult = jdbcUtil.excuteQuery(sql, null);
            result.put("list", listResult);
            result.put("station", station);
            apiResponse = ApiResponse.create(true, Code.SUCCESS,result);
        }catch (Exception e){
            logger.error("getList",e);
            apiResponse = ApiResponse.create(false,e.getMessage(),Code.COMMON_SERVER_ERROR.code,null);
        } finally {
        	if(null != jdbcUtil) {
        		jdbcUtil.closeAll();
        	}
        }
        return apiResponse;
    }
	
	@RequestMapping("/getRecentData")
    @ResponseBody
    public ApiResponse getRecentData(QueryStationRainReportDto dto){
        ApiResponse apiResponse;
        JDBCUtil jdbcUtil = null;
        try {
            if (dto == null || null == dto.getStationId()) {
                apiResponse = ApiResponse.create(false,null, Code.BAD_PARAMS.code,null);
                return apiResponse;
            }
            Map map = stationService.queryStationById(dto.getStationId());
            if (map == null || map.get("station") == null) {
                apiResponse = ApiResponse.create(false,null, Code.NO_PROJECT.code,null);
                return apiResponse;
            }
            Station station = (Station)map.get("station");
            
            String sql = "select va from tb_dy_rainfall_data_gather";
            String condition = " where station_id = " + dto.getStationId() + " AND DATE_FORMAT(create_time,'%Y-%m-%d') = curdate()";
            Map result = new HashMap();
            sql = sql +  " " + condition + "  order by create_time desc limit 0,1 ";
            jdbcUtil = new JDBCUtil(station.getJdbcDriver(), station.getJdbcUrl(),station.getJdbcUserName(), station.getJdbcPassword());
            Object today = jdbcUtil.excuteQuery(sql, null);
            result.put("today", today);
            
            String currentHourStr = DateFormatUtils.format(new Date(), "yyyy-MM-dd HH");
            sql = "select va from tb_dy_rainfall_data_gather";
            condition = " where station_id = " + dto.getStationId() + " AND create_time like '" + currentHourStr + "%'";
            sql = sql +  " " + condition + "  order by create_time desc limit 0,1 ";
            Object currentHour = jdbcUtil.excuteQuery(sql, null);
            result.put("currentHour", currentHour);
            
            sql = "select va from tb_dy_rainfall_data_gather";
            condition = " where station_id = " + dto.getStationId() + " AND DATE_FORMAT(create_time,'%Y-%m-%d') = date_sub(curdate(),interval 1 day)";
            sql = sql +  " " + condition + "  order by create_time desc limit 0,1 ";
            Object yesterday = jdbcUtil.excuteQuery(sql, null);
            result.put("yesterday", yesterday);
            
            sql = "select va from tb_dy_rainfall_data_gather";
            condition = " where station_id = " + dto.getStationId() + " AND DATE_FORMAT(create_time,'%Y-%m-%d') = date_sub(curdate(),interval 2 day)";
            sql = sql +  " " + condition + "  order by create_time desc limit 0,1 ";
            Object beforeYesterday = jdbcUtil.excuteQuery(sql, null);
            result.put("beforeYesterday", beforeYesterday);
            
            sql = "select sum(va) va from tb_dy_rainfall_data_gather";
            condition = " where station_id = " + dto.getStationId() + " AND create_time >  DATE_SUB(CURDATE(),INTERVAL 2 DAY)  AND create_time < DATE_SUB(CURDATE(),INTERVAL -1 DAY) ";
            sql = sql +  " " + condition;
            Object total = jdbcUtil.excuteQuery(sql, null);
            result.put("total", total);
            
            result.put("station", station);
            apiResponse = ApiResponse.create(true, Code.SUCCESS,result);
        }catch (Exception e){
            logger.error("getReportData",e);
            apiResponse = ApiResponse.create(false,e.getMessage(),Code.COMMON_SERVER_ERROR.code,null);
        } finally {
        	if(null != jdbcUtil) {
        		jdbcUtil.closeAll();
        	}
        }
        return apiResponse;
    }
	
    @RequestMapping("/getReportData")
    @ResponseBody
    public ApiResponse getReportData(QueryStationRainReportDto dto){
        ApiResponse apiResponse;
        JDBCUtil jdbcUtil = null;
        try {
            if (dto == null || dto.getStationId() == null) {
                apiResponse = ApiResponse.create(false,null, Code.BAD_PARAMS.code,null);
                return apiResponse;
            }
            Map map = stationService.queryStationById(dto.getStationId());
            if (map == null || map.get("station") == null) {
                apiResponse = ApiResponse.create(false,null, Code.NO_PROJECT.code,null);
                return apiResponse;
            }
            Station station = (Station)map.get("station");
            String sql = "select va, date_format(create_time, '%Y-%m-%d %H:%i:%S') createTime from tb_dy_rainfall_data_gather";
            String condition = " where station_id = " + dto.getStationId(); 
            Map result = new HashMap();
            Date date1 = DateUtil.parseDate(dto.getBeginTime());
            Date date2 = DateUtil.parseDate(dto.getEndTime());
            int count = DateUtil.getBetweenDays(date1,date2).intValue();
            condition = " where 1=1 ";
       		if(count == 1) {
        		if(StringUtils.isNotBlank(dto.getBeginTime())) {
        			condition = condition + " and create_time >='" + dto.getBeginTime() + "'";
        		}
        		if(StringUtils.isNotBlank(dto.getEndTime())) {
        			condition = condition + " and create_time <'" + dto.getEndTime() + "'";
        		}
        		condition = condition + " GROUP BY HOUR(create_time)  ";
    		} else if(count == 7) {
    			sql = "select va, date_format(min(create_time), '%Y-%m-%d %H:%i:%S') createTime  from tb_dy_rainfall_data_gather";
        		if(StringUtils.isNotBlank(dto.getBeginTime())) {
        			condition = condition + " and create_time >='" + dto.getBeginTime() + "'";
        		}
        		if(StringUtils.isNotBlank(dto.getEndTime())) {
        			condition = condition + " and create_time <'" + dto.getEndTime() + "'";
        		}
    			condition = condition + " GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d %H') ";
    		} else if (count <=31 ) {
    			sql = "select va, date_format(min(create_time), '%Y-%m-%d %H:%i:%S') createTime  from tb_dy_rainfall_data_gather";
        		if(StringUtils.isNotBlank(dto.getBeginTime())) {
        			condition = condition + " and create_time >='" + dto.getBeginTime() + "'";
        		}
        		if(StringUtils.isNotBlank(dto.getEndTime())) {
        			condition = condition + " and create_time <'" + dto.getEndTime() + "'";
        		}
    			condition = condition + " GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d')";
    		}else {
    			sql = "select va, date_format(min(create_time), '%Y-%m-%d %H:%i:%S') createTime  from tb_dy_rainfall_data_gather";
    			List<String> dateList = DateUtil.getMonday(date1,date2);
    			String dateListStr = "";
    			for(String d : dateList) {
    				if(StringUtils.isNotBlank(dateListStr)) {
    					dateListStr = dateListStr + ",'" + d + "'";
    				} else {
    					dateListStr = "('" + d +"'";
    				}
    			}
    			if(StringUtils.isNotBlank(dateListStr)) {
    				dateListStr = dateListStr + ")";
    				condition = condition + " and DATE_FORMAT(create_time,'%Y-%m-%d')" + " in " + dateListStr;
    				condition = condition + " GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d')";
    			}
    		}
            sql = sql + " " + condition + "  order by create_time asc";
            jdbcUtil = new JDBCUtil(station.getJdbcDriver(), station.getJdbcUrl(),station.getJdbcUserName(), station.getJdbcPassword());
            List<Object> listResult = jdbcUtil.excuteQuery(sql, null);
            result.put("list", listResult);
            result.put("station", station);
            apiResponse = ApiResponse.create(true, Code.SUCCESS,result);
        }catch (Exception e){
            logger.error("getReportData",e);
            apiResponse = ApiResponse.create(false,e.getMessage(),Code.COMMON_SERVER_ERROR.code,null);
        } finally {
        	if(null != jdbcUtil) {
        		jdbcUtil.closeAll();
        	}
        }
        return apiResponse;
    }
}
